package com.api.util;

import com.api.pojo.Case;
import com.api.pojo.Rest;
import com.api.pojo.VariableConfiguration;
import com.api.pojo.WriteBackData;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

public class ExcelUtil {
//保存rowIdentfier跟行号的映射
    public static List<WriteBackData> writeBackDatas = new ArrayList<WriteBackData>();
    public static Map<String ,Integer> rowIdentifiedRownumMapping = new HashMap<String, Integer>();
    public static Map<String ,Integer> cellNameCellnumMapping = new HashMap<String, Integer>();
    static {
        loadRowNameCellnumMappings(PropertiesUtil.getCaseFilePath(),"用例");
    }

    public static void loadRowNameCellnumMappings(String filePath, String sheetName) {
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(new File(filePath));
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheet(sheetName);
            Row titieRow = sheet.getRow(0);
            int lastCellnum = titieRow.getLastCellNum();
            for(int i =0;i<lastCellnum;i++){
                Cell cell =titieRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                cell.setCellType(CellType.STRING);
                String title = cell.getStringCellValue().trim();
                title = title.substring(0,title.indexOf("("));
                int cellnum = cell.getAddress().getColumn();
                cellNameCellnumMapping.put(title,cellnum);

            }
            //rowIdentfier和行号映射
            int lastRownum = sheet.getLastRowNum();
            for(int i=1;i<=lastRownum;i++){
                Row row = sheet.getRow(i);
                Cell cell = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                cell.setCellType(CellType.STRING);
                String rowIdentfier = cell.getStringCellValue().trim();
                int rownum = cell.getAddress().getRow();
                rowIdentifiedRownumMapping.put(rowIdentfier,rownum);


            }



        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //要读取的文件路径，表单名
    /*
    * @param filePath:要读取的文件路径
    * @param sheetName 表单名
    * @param cellNames  要读取的列名 ，设计为数组，要取哪些列，就读取哪些列
    * 读取标题行数据，然后根绝标题行进行拿数据，并且去掉空行。存放于复合数组中，最终将数据放于二维数组返回
    *
    *
    * */
    public static Object[][] read(String filePath, String sheetName, String[] cellNames) {
        InputStream iStream=null;
        //每行放到一个内层list中，多行放到外层list中
        ArrayList<ArrayList<String>> groups=null;
        try{
            iStream = new FileInputStream(new File(filePath));
        Workbook workbook = WorkbookFactory.create(iStream);
        Sheet sheet = workbook.getSheet(sheetName);
        //存放列标题和索引
        Map<String,Integer> cellNameAndCellnum = new HashMap<String,Integer>();
        //获取所有的标题数据，以及每个标题所在的索引列
        Row titleRow = sheet.getRow(0);
        //取出表单中列的个数
        int lastCellNum = titleRow.getLastCellNum();

        //循环取出标题行的每一列，即每个标题
        for(int i =0;i<lastCellNum;i++){
            Cell cell = titleRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            cell.setCellType(CellType.STRING);
            //标题
            String title = cell.getStringCellValue().trim();
            //取出当前标题对应的列索引
            int cellnum = cell.getAddress().getColumn();
            cellNameAndCellnum.put(title,cellnum);

        }
        //获取最后一行的索引
            int lastRownum = sheet.getLastRowNum();
        //datas = new Object[lastRownum][cellNames.length];
            groups = new  ArrayList<ArrayList<String>>();
        for(int i =1;i<=lastRownum;i++){
            ArrayList<String> cellValuesPerRow = new ArrayList<String>();
            Row row = sheet.getRow(i);
            if(isEmpty(row)){
                continue;
            }
            //取出行对应的列数据
            for(int j=0;j<cellNames.length;j++){
                String cellName=cellNames[j];
                //根据列名，从map中获取列索引
                int cellnum = cellNameAndCellnum.get(cellName);
                Cell cell = row.getCell(cellnum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                cell.setCellType(CellType.STRING);
                String value = cell.getStringCellValue().trim();
                //将值放到数组中
                cellValuesPerRow.add(value);
            }
            groups.add(cellValuesPerRow);

        }
        System.out.println("");
        //取出所有行（标题行除外）
        }catch (Exception e){
            e.printStackTrace();
        }finally {

            if(iStream!=null){
                try {
                    iStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }
        }

    //将list集合中的数据保存到二维数组中返回
       Object[][] datas =  listToArray(groups);
        //从数组中拿到所有的1号接口的测试数据
        return datas;
    }
  //集合转换为数组
    private static Object[][] listToArray(ArrayList<ArrayList<String>> groups) {
        //获取总共的组数
        int size1=groups.size();
        //取出第一组的数据个数
        int size2=groups.get(0).size();
        //声明二维数组
        Object[][] datas=new Object[size1][size2];
        //循环
        for(int i=0;i<size1;i++){
            //取出每一组
            ArrayList<String> group = groups.get(i);
            //循环
            for(int j=0;j<size2;j++){
                //取出每组中的每个数据
                String value =  group.get(j);
                //将数据保存到二维数组中
                datas[i][j]=value;
            }

        }
        //返回二维数组
        return datas;
    }
//判断是否为空行
    private static boolean isEmpty(Row row){
        int lastCellnum = row.getLastCellNum();
        for(int i=0;i<lastCellnum;i++){
            Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            cell.setCellType(CellType.STRING);
            String value = cell.getStringCellValue();
            if(value!=null&&value.trim().length()>0){
                return false;
            }
        }
         return true;
        }


//使用反射实现两个表单解析
    public static void loadDatas(String filePath,String sheetName,Class clazz) {
        //获取workbook对象
        InputStream inputStream;
        try {
            inputStream = new FileInputStream(new File(filePath));
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheet(sheetName);
            int lastRownum = sheet.getLastRowNum();
            //先拿到标题行及第一行
            Row titleRow = sheet.getRow(0);
            //获取总列数
            int lastCellnum = titleRow.getLastCellNum();
            //标题数组，管理title
            String[] titles = new String[lastCellnum];

            //获取标题列
            for (int i = 0; i < lastCellnum; i++) {
                Cell cell = titleRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                cell.setCellType(CellType.STRING);
                //获取列的值
                String title = cell.getStringCellValue().trim();//ApiId(接口编号)
                //进行截取去掉汉字
                title = title.substring(0, title.indexOf("("));
                titles[i] = title;

            }

            //解析所有的数据行
            for (int i = 1; i <= lastRownum; i++) {
                //每执行一次就会创建一个Rest对象
                //Rest rest = new Rest();
                Object object = clazz.newInstance();
                Row dataRow = sheet.getRow(i);
                //根据获取的行再获取最大列数
                //反射，提供字节码
                //Class<Rest> clazz = Rest.class;
                for (int j = 0; j < lastCellnum; j++) {
                    //取出每一列
                    Cell cell = dataRow.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    //设置列的数据类型
                    cell.setCellType(CellType.STRING);
                    //获取列的值
                    String value = cell.getStringCellValue().trim();
                    //取出此列对应的标题
                    String title = titles[j].trim();
                    //拼接方法名
                    String methodName = "set" + title;//
                    //反射调用
                    Method method = clazz.getMethod(methodName, String.class);
                    method.invoke(object, value);
                }
                //添加到集合
                if(object instanceof Case){
                    Case cs = (Case) object;
                    CaseUtil.cases.add(cs);

                }else if(object instanceof Rest){
                    Rest rs = (Rest) object;
                    RestUtil.rests.add(rs);

                }else if(object instanceof VariableConfiguration){
                    VariableConfiguration variableConfiguration = (VariableConfiguration) object;
                    VariableConfigurationUtil.configurationList.add(variableConfiguration);
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    /**单次写入
     * @param filePath
     * @param sheetName
     * @param rowIdentfier
     * @param cellName
     * @param result
     */
    public static void writeData(String filePath,String sheetName,String rowIdentfier, String cellName, String result) {
        //根据rowIdentfier取出行号
        int rowNum = rowIdentifiedRownumMapping.get(rowIdentfier);
        //根据列名cellName取出列号
        int cellNum = cellNameCellnumMapping.get(cellName);
        InputStream inputStream = null;
        OutputStream outputStream=null;
        try {
            inputStream = new FileInputStream(new File(filePath));
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheet(sheetName);
            Row row = sheet.getRow(rowNum);
            Cell toBeProcessedCell = row.getCell(cellNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            toBeProcessedCell.setCellType(CellType.STRING);
            //将数据写到指定位置
            toBeProcessedCell.setCellValue(result);
            outputStream = new FileOutputStream(new File(filePath));
            //调用write方法将数据写到excel中
            workbook.write(outputStream);

        }catch (Exception e){
             e.printStackTrace();

        }finally {
            //关闭资源
                try {
                    if(inputStream!=null){
                        inputStream.close();
                    }
                    if(outputStream!=null){
                        outputStream.close();
                    }

                    } catch (Exception e2) {
                    e2.printStackTrace();
                }
            }
    }

    public static void main(String[] args){
        Set<Map.Entry<String,Integer>> entries = cellNameCellnumMapping.entrySet();
        for(Map.Entry<String,Integer> entry:entries){
            System.out.println(entry.getKey()+"-"+entry.getValue());

        }
        Set<Map.Entry<String,Integer>> entrie = rowIdentifiedRownumMapping.entrySet();
        for(Map.Entry<String,Integer> entry:entrie){
            System.out.println(entry.getKey()+"-"+entry.getValue());

        }
        batchWriteBackDatas(PropertiesUtil.getCaseFilePath());

    }

    /**批量将测试结果写入Excel
     * @param rowIdentfier
     * @param cellName
     * @param result
     */
    public static void saveWriteBackData(String sheetName,String rowIdentfier, String cellName, String result) {
        WriteBackData writeBackData = new WriteBackData();
        writeBackData.setBelongSheet(sheetName);
        writeBackData.setRowIndentifier(rowIdentfier);
        writeBackData.setCellName(cellName);
        writeBackData.setResult(result);
        writeBackDatas.add(writeBackData);

    }


    public static void batchWriteBackDatas(String filepath) {
        //根据rowIdentfier取出行号
        System.out.println("读写数据");
        InputStream inputStream = null;
        OutputStream outputStream=null;
        try {
            inputStream = new FileInputStream(new File(filepath));
            Workbook workbook = WorkbookFactory.create(inputStream);
            //分类数据
            //Map<> map = new HashMap()
            //Sheet sheet = workbook.getSheet();
            for(WriteBackData writeBackData:writeBackDatas) {
                //判断属于哪个表单
                Sheet sheet = workbook.getSheet(writeBackData.getBelongSheet());
                String rowIdentfie = writeBackData.getRowIndentifier().trim();
                int rownum = rowIdentifiedRownumMapping.get(rowIdentfie);
                System.out.println("行号是：" + rownum);
                Row row = sheet.getRow(rownum);
                String cellname = writeBackData.getCellName().trim();
                int cellnum = cellNameCellnumMapping.get(cellname);
                Cell toBeProcessedCell = row.getCell(cellnum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                toBeProcessedCell.setCellType(CellType.STRING);
                String result = writeBackData.getResult().trim();
                //将数据写到指定位置
                toBeProcessedCell.setCellValue(result);
            }

            outputStream = new FileOutputStream(new File(filepath));
            //调用write方法将数据写到excel中
            workbook.write(outputStream);

        }catch (Exception e){
            e.printStackTrace();

        }finally {
            //关闭资源
            try {
                if(inputStream!=null){
                    inputStream.close();
                }
                if(outputStream!=null){
                    outputStream.close();
                }

            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }




    }
    }



